#!C:/Perl/bin/perl.exe -w
use CGI;
use JSON;
use DBI;
use strict;

# Capture error output for debugging.
*STDERR = *STDOUT;

my $outDir = '';
my $logFile = "ALEKSlog.txt";
my $submissionFile = "ALEKSsubmission.txt";

# Strand names and letters, indexed in the same order as the report parser.
my @strands = (
    "Number Sense and Operations",
    "Patterns, Relations, and Algebra",
    "Geometry",
    "Measurement",
    "Data Analysis, Statistics, and Probability"
);
my @strandLetters = ('N', 'P', 'G', 'M', 'D');

# Get the HTTP request, $q.
my $q = CGI->new;
# Extract our parameters.
my $reportAsOf = $q->param('asOf');  # when data was extracted from ALEKS
my $report_record = $q->param('report_record');
my $assess_date = $q->param('assess_date');
my $teacher = $q->param('teacher');
my $report_grade = $q->param('report_grade');
my $report_course = $q->param('report_course');
my $report_student = $q->param('report_student');
my $studentLastFirst = $q->param('studentLastFirst');
my $studentLastName = $studentLastFirst =~ /^([^,]*),/ ? $1 : $report_student;
my $report_json = $q->param('report_json');
my $report = decode_json($report_json);  

#--------------------------------------
# report tree data structure:
# report array
#     strand hash
#         'strand' number
#         'standards' array
#             standard hash
#                 'grade' string
#                 'strand' index
#                 'standardNum' number
#                 'standardText' string
#                 'numItems' count
#                 'numMastered' count
#                 'mastered' array
#                     item name string
#                 'notMastered' array
#                     item name string
#--------------------------------------

# Generate a unique identifier for this submission.
# Student name & Unix-style time stamp (number of seconds since Jan 1, 1970).
my $batch = $report_student;
$batch =~ s/\W//g;
$batch .= time;


# Log this submission.
open LOG, '>>', $outDir . $logFile or die "Can't open log file $outDir$logFile: $!";
print LOG scalar(localtime(time)), ' received ', $report_course, ' for ', $report_student, ' [', $batch, ']', "\n";
close LOG;

# Cache the submission for debugging.
## my %submission = (guid => $batch, teacher => $teacher, grade => $report_grade, course => $report_course, 
##     student => $report_student, asOf => $reportAsOf, record => $report_record, report => $report);
## my $submissionJSON = encode_json \%submission;
## open CACHE, '>>', $outDir . $submissionFile or die "Can't open submission cache file: $!";
## print CACHE $submissionJSON, "\n";
## close CACHE;

# Store data in a SQLite database.
my $dbfile = 'aleks.sqlite';  # in the current directory
my $db = DBI->connect("dbi:SQLite:dbname=$dbfile","","")  or die $DBI::errstr;
my @row;  # a temporary row holder

# Find or add the teacher.
my $teacherRow;  # will hold our teacher's rowid
my $teacherSelect = $db->prepare( "select rowid from teacher where name = ?;" )  or die $db->errstr;
$teacherSelect->execute( $teacher )  or die $teacherSelect->errstr;
if ( @row = $teacherSelect->fetchrow_array ) {
    $teacherRow = $row[0];
} else {
    if ( defined $teacherSelect->err ) {
        die $teacherSelect->errstr;
    } else {
        # Add teacher.
        $db->do( "insert into teacher values(null,?);", undef, $teacher ) or die $db->errstr;
        $teacherRow = $db->func('last_insert_rowid');
    }
}

# Find or add the course.
my $courseRow;  # will hold our course's rowid
my $courseSelect = $db->prepare( "select rowid from course where name = ?;" )  or die $db->errstr;
$courseSelect->execute( $report_course )  or die $courseSelect->errstr;
if ( @row = $courseSelect->fetchrow_array ) {
    $courseRow = $row[0];
} else {
    # Add course.
    $db->do( "insert into course values(null,?);", undef, $report_course )  or die $db->errstr;
    $courseRow = $db->func('last_insert_rowid');
}

# Find or add the student.
my $studentRow;  # will hold our student's rowid
my $studentSelect = $db->prepare( "select rowid from student where name = ?;" )  or die $db->errstr;
$studentSelect->execute( $report_student )  or die $studentSelect->errstr;
if ( @row = $studentSelect->fetchrow_array ) {
    $studentRow = $row[0];
} else {
    # Add student.
    $db->do( "insert into student values(null,?,?);", undef, $report_student, $studentLastName )  or die $db->errstr;
    $studentRow = $db->func('last_insert_rowid');
}

# Find or add a report/batch record.
my $rptRow;  # will hold our report rowid
my $selectReport = $db->prepare( "select rowid from report where record = ?;" ) or die $db->errstr;

my $addReport = $db->prepare( "insert into report values(null,?,?,?,?,?,?,?,?);" )  or die $db->errstr;
$addReport->execute( $batch, $report_record, $assess_date, $report_grade, 
    $studentRow, $teacherRow, $courseRow, $reportAsOf  )  or die $addReport->errstr;
$rptRow = $db->func('last_insert_rowid');

# Match MCAS standards and create summary report records (items mastered / total items).
# Match ALEKS items and create or update mastery records for this student.
foreach ( @{$report} ) {  # each strand
    my $strand = $_->{strand};  # strand number
    foreach ( @{$_->{standards}} ) {  # each standard
        my $stdRow;  # will hold rowid for each standard as we process it
        my $rptStdRow;  # will hold rowid for each reported standard record as we process it
        my $stdSelect = $db->prepare( 
            "select rowid from standard where grade = ? and strand = ? and number = ?;" )  or die $db->errstr;
        $stdSelect->execute( $_->{grade}, $_->{strand}, $_->{standardNum} )  or die $stdSelect->errstr;
        if ( @row = $stdSelect->fetchrow_array ) {
            $stdRow = $row[0];
        } else {
            # Add this standard.
            $db->do( "insert into standard values(null,?,?,?,?);", undef,
              $_->{grade}, $_->{strand}, $_->{standardNum}, $_->{standardText} )  or die $db->errstr;
            $stdRow = $db->func('last_insert_rowid');
        }
        # Create a 'reported standard' record.
        my $addRptStd = $db->prepare( "insert into reportedStandard values(null,?,?,?,?);" )  or die $db->errstr;
        $addRptStd->execute( $rptRow, $stdRow, $_->{numItems}, $_->{numMastered} )  or die $addRptStd->errstr;
        $rptStdRow = $db->func('last_insert_rowid');
        # Check off the lists of mastered and unmastered items.
        foreach ( map([1, $_], @{$_->{mastered}}), map([0, $_], @{$_->{notMastered}}) ) {
            (my $mastered, my $item) = @{$_};
            my $itemRow;  # item rowid
            my $itemSelect = $db->prepare( "select rowid from item where standard = ? and text = ?;" )  or die $db->errstr;
            $itemSelect->execute( $stdRow, $item ) or die $itemSelect->errstr;
            if ( @row = $itemSelect->fetchrow_array ) {  # item already present
                $itemRow = $row[0];
            } else {  # add item
                $db->do( "insert into item values(null,?,?);", undef, $item, $stdRow )  or die $db->errstr;
                $itemRow = $db->func('last_insert_rowid');
            }
            # Attend to the student mastery record.
            my $masteryRow;
            my $masterySelect = $db->prepare( "select rowid from mastery where student = ? and item = ?;" )  or die $db->errstr;
            $masterySelect->execute( $studentRow, $itemRow )  or die $masterySelect->errstr;
            if ( @row = $masterySelect->fetchrow_array ) {  # mastery record present
                $masteryRow = $row[0];
                # Update the mastery record.
                $db->do( "update mastery set mastered = ?, reportedStandard = ? where rowid = ?;", undef, 
                  $mastered, $rptStdRow, $masteryRow )  or die $db->errstr;
            } else {  # add a mastery record
                $db->do( "insert into mastery values(null,?,?,?,?);", undef,
                  $studentRow, $itemRow, $mastered, $rptStdRow )  or die $db->errstr;
            }
        }
    }
}

# Disconnect from the database
$db->disconnect;

# Compute some statistics.
my $totalItems = 0;
my $totalMasteredItems = 0;
my @strandItems = ();
my @strandMasteredItems = ();
foreach ( @{$report} ) {  # each strand
    my $strand = $_->{strand};
    foreach ( @{$_->{standards}} ) {
        $totalItems += $_->{numItems};
        $strandItems[$strand] += $_->{numItems};
        $totalMasteredItems += $_->{numMastered};
        $strandMasteredItems[$strand] += $_->{numMastered};
    }
}
sub percent {
    # Avoid dividing by zero.
    my $numerator = shift;
    my $denominator = shift;
    if ($denominator == 0) {
        return 0;
    } else {
        return $numerator / $denominator * 100;
    }
}

# Create pie charts.
sub percentPie {
    # E.g., print percentPie(50, 70); would should 50% items mastered in a 70x70 pixel graphic.
    my $percent = shift;
    my $size = shift;
    if (!defined $size) { $size = 70 }
    $size = sprintf('%.0f', $size);
    my $data = sprintf('%.0f', $percent) . ',' . sprintf('%.0f', 100 - $percent);
    my $chartPiece1 = '<img class="pieImg" src="http://chart.apis.google.com/chart?chs=SZxSZ&cht=p&chco=008000&chd=t:';
    my $chartPiece2 = '&chp=4.71" width="SZ" height="SZ" alt="percentage of items mastered" />';
    my $html = $chartPiece1 . $data . $chartPiece2;
    $html =~ s/SZ/$size/g;
    return $html;
}


# Acknowledge submission.
sub returnReport {
    my $styleBlock=<<EOT;
td { vertical-align: top }
.itemTable { margin-left: 0.25in }
.itemColumn { padding-right: 15px; text-align: left }
caption { text-align: left }
.mastery { font-style: italic }
.pieImg { vertical-align: middle }
EOT
    print $q->header,
    $q->start_html(-title=>'ALEKS data submitted',
        -style=>{'-code'=>$styleBlock}
    ),
    $q->h1($report_course),
    $q->h2($report_student),
    $q->p(),
    'data extracted ' . $reportAsOf . '<br>',
    'from ALEKS record ' . $report_record . '<br>',
    'assessment date ' . $assess_date,
    $q->p({-class=>'mastery'}, 'Has mastered ' . $totalMasteredItems . ' of ' . $totalItems . 
        ', or ' . sprintf('%.0f', percent($totalMasteredItems, $totalItems)) . '% of ALEKS items overall.',
        percentPie(percent($totalMasteredItems, $totalItems), 100) );
    foreach (@{$report}) {
        print $q->h3($strands[$_->{strand}]);
        print $q->p({-class=>'mastery'}, 'Has mastered ' . $strandMasteredItems[$_->{strand}] . ' of ' . $strandItems[$_->{strand}] . 
            ', or ' . sprintf('%.0f', percent($strandMasteredItems[$_->{strand}], $strandItems[$_->{strand}])) . '% of ALEKS items for this strand.',
            percentPie(percent($strandMasteredItems[$_->{strand}], $strandItems[$_->{strand}]), 70) );
        foreach (@{$_->{standards}}) {
            print $q->h4($_->{grade} . '.' . $strandLetters[$_->{strand}] . '.' . $_->{standardNum} . 
                '&nbsp;&nbsp;' . $_->{standardText});
            print $q->table({-class=>'itemTable'}, 
                $q->caption( {-class=>'mastery'}, $_->{numMastered} . ' out of ' . $_->{numItems} . ' ALEKS items mastered',
                    percentPie(percent($_->{numMastered}, $_->{numItems}), 50) ),
                $q->Tr( [
                    $q->th({-class=>'itemColumn'}, ['mastered', 'not mastered']),
                    $q->td({-class=>'itemColumn'}, [
                        join('<br>', @{$_->{mastered}}), 
                        join('<br>', @{$_->{notMastered}})
                        ])
                    ] )
            );
        }
    }
    # Raw parameter values:
    #print $q->table(
    #          $q->Tr( [
    #              $q->td(['report_grade', $report_grade]),
    #              $q->td(['report_course', $report_course]),
    #              $q->td(['report_student', $report_student]),
    #              $q->td(['report_json', $report_json])
    #          ] )
    #      );

    print $q->end_html;
}

sub respond {
    print $q->header,
    $q->start_html(-title=>'ALEKS data submitted'),
    $q->p($report_student),
    $q->p($totalItems . ' items reported'),
    $q->end_html;
}

# returnReport;
respond;
